package com.ats.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ats.dao.StudentDao_xh;
import com.ats.model.Student;
import com.ats.util.JDBCUtil;

import net.sf.json.JSONArray;
/**
 * 学生个人功能的DAO层实现类
 * @author xh
 *
 */
public class StudentDaoImpl_xh implements StudentDao_xh{

	@Override
	public String FindSnameBySid(int sid) {
		// TODO Auto-generated method stub
		String sname ="";
		StringBuffer sql = new StringBuffer();
		sql.append(" select name from ats_student");
		sql.append(" where sid= ?");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,sid);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				sname=rs.getString("name");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sname;
	}

	@Override
	public JSONArray FindStudentByUid(int uid) {
		// TODO Auto-generated method stub
		JSONArray jso = new JSONArray();
		StringBuffer  sql = new StringBuffer();
		sql.append(" select ats_student.sid,ats_student.`name`,ats_student.age,");
		sql.append(" ats_student.address,ats_student.number,ats_user.uid,");
		sql.append(" ats_user.uname,ats_user.upsw");
		sql.append(" from ats_student");
		sql.append(" inner join ats_user");
		sql.append(" on ats_student.sid = ats_user.sid");
		sql.append(" where ats_user.uid = ? ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,uid);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Student stu = new Student();
				stu.setSid(rs.getInt("sid"));
				stu.setName(rs.getString("name"));
				stu.setAge(rs.getInt("age"));
				stu.setAddress(rs.getString("address"));
				stu.setNumber(rs.getString("number"));
				stu.setUid(rs.getInt("uid"));
				stu.setUname(rs.getString("uname"));
				stu.setUpsw(rs.getString("upsw"));
				jso.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return jso;
	}

	@Override
	public boolean updateStudentBySid(String name, int age, String address, String number, int sid) {
		// TODO Auto-generated method stub
		boolean flag =false;
		StringBuffer  sql = new StringBuffer();
		sql.append("update ats_student set ats_student.`name`=?,");
		sql.append("ats_student.age=? ,ats_student.address=?,");
		sql.append("ats_student.number=?");
		sql.append(" where sid= ?");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, name);
			pst.setInt(2, age);
			pst.setString(3, address);
			pst.setString(4, number);
			pst.setInt(5, sid);
			int i =pst.executeUpdate();
			if(i>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public boolean updateStudentPswBySid(String uname, String upsw, int sid) {
		// TODO Auto-generated method stub
		boolean flag =false;
		StringBuffer  sql = new StringBuffer();
		sql.append("update ats_user set uname=?,upsw=? ");
		sql.append(" where sid=?");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, uname);
			pst.setString(2, upsw);
			pst.setInt(3, sid);
			int i =pst.executeUpdate();
			if(i>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public List<Student> findStudentSid() {
		// TODO Auto-generated method stub
		List<Student> stus = new ArrayList<Student>();
		StringBuffer  sql = new StringBuffer();
		sql.append("select sid from ats_student");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Student stu = new Student();
				stu.setSid(rs.getInt("sid"));
				stus.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return stus;
	}

	@Override
	public int findSidByUid(int uid) {
		// TODO Auto-generated method stub
		int sid=0;
		StringBuffer  sql = new StringBuffer();
		sql.append(" select sid from ats_user");
		sql.append(" where uid= ?");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,uid);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				sid=rs.getInt("sid");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sid;
	}

	

}
